package db;

import java.security.MessageDigest;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DatabaseKobling {

    /*
    String dbName = "aspen_v12";
    String userName = "aspen_v12";
    String password = "aspen@hinv12";
    //port:3306
    */

    public static void main(String[] args) {
        try {
            
//            System.out.println(getUser(getConnection(), "Tanita", "qwerty"));
            System.out.println(getAreaId("Area1"));
            
//            if(addStructure(getConnection(), null, "Hus", 100, 100, "C", "Heat pump", "Coal", 9))
//               System.out.println("yay...1");
//            if(addHouseholdArticle(getConnection(), "toaster", 10, "Kitchen_equipment", "Admin"))
//               System.out.println("yay...2");
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseKobling.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    
    //Setter opp connection til databasen vår
    public static Connection getConnection() throws SQLException {
        Connection con = null;
        
        String database = "kark.hin.no/aspen_v12?";
        String username = "aspen_v12";
        String password = "aspen@hinv12";
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String connectionUrl = "jdbc:mysql://" + database
                    + "user=" + username + "&password=" + password;
            con = DriverManager.getConnection(connectionUrl);
        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.toString());
        } catch (ClassNotFoundException cE) {
            System.out.println("Class Not Found Exception: " + cE.toString());
        }

        return con;
    }
    
    
    //Hente liste med categories
    public static ArrayList<String> getCategories() throws SQLException{
        Connection con = getConnection();

        
        ArrayList<String> categories = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT * FROM categories";
        ResultSet rs = sqlRead(con, SQL);
        
        while (rs.next()) {
            categories.add(rs.getString("category"));
        }
        
        return categories;
    }
    
    //Hente liste med varmekilder
    public static ArrayList<String> getHeatsources() throws SQLException{
        Connection con = getConnection();
        
        ArrayList<String> heatsources = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT * FROM heatsources";
        ResultSet rs = sqlRead(con, SQL);
        
        while (rs.next()) {
            heatsources.add(rs.getString("heatsource"));
        }
        
        return heatsources;
    }
    
    public static ArrayList<String> getsHeatsources(String pHeatsource) throws SQLException{
        Connection con = getConnection();
        
        ArrayList<String> sHeatsources = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT * FROM heatsources WHERE heatsource !=\"" + pHeatsource+ "\"";
        ResultSet rs = sqlRead(con, SQL);
        
        while (rs.next()) {
            sHeatsources.add(rs.getString("heatsource"));
        }
        
        return sHeatsources;
    }
    
    //Henter ut u-verdi navn til en liste
    public static ArrayList<String> getUValues() throws SQLException{
        Connection con = getConnection();
        
        ArrayList<String> uValue = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT name FROM u_values";
        ResultSet rs = sqlRead(con, SQL);
        
        while (rs.next()) {
            uValue.add(rs.getString("name"));
        }
        return uValue;
    }
    
    public static ArrayList<Double> getUValues(String UValues) throws SQLException{
        Connection con = getConnection();
        
        ArrayList<Double> uValue = new ArrayList<Double>();
        
        String SQL = "SELECT * FROM u_values WHERE name = \"" + UValues + "\"";
        ResultSet rs = sqlRead(con, SQL);
        
        //MÅ LEGGES TIL PREPARED STATEMENT
        /*
        PreparedStatement pstmt = con.prepareStatement(SQL);
        pstmt.executeUpdate();
        */
        
        if(rs.next()){
            uValue.add(rs.getDouble("outer_wall"));
            uValue.add(rs.getDouble("roof"));
            uValue.add(rs.getDouble("floor"));
            uValue.add(rs.getDouble("doors_and_windows"));
        }
        else{
            System.out.println("BUUUUUUUUGS!!!");
        }

        


        return uValue;
    }
    
    //Hente bygninger for bruker
    public static ArrayList<String> getUserStructures() throws SQLException{
        Connection con = getConnection();

        
        ArrayList<String> structures = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT name FROM structure"; //WHERE users_user = \"" + user + "\"";
        ResultSet rs = sqlRead(con, SQL);
        
        while (rs.next()) {
            structures.add(rs.getString("name"));
        }
        
        return structures;
    }
    
    public static int getStructureId(String name)throws SQLException
    {
        int id = 0;
        String SQL ="SELECT idstructure FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        id = ((Number) rs.getObject(1)).intValue();}
        return id;
    }
    
    public static String getStructureEnergyclass(String name)throws SQLException
    {
        String eClass = "";
        String SQL ="SELECT energy_class FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        eClass = rs.getString(1);}
        return eClass;
    }
    
    public static int getStructureAge(String name)throws SQLException
    {
        int age = 0;
        String SQL ="SELECT age FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        age = ((Number) rs.getObject(1)).intValue();}
        return age;
    }
    
    public static int getStructureArea(String name)throws SQLException
    {
        int area = 0;
        String SQL ="SELECT living_area FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        area = ((Number) rs.getObject(1)).intValue();}
        return area;
    }
    
    public static String getStructurePHeatsource(String name)throws SQLException
    {
        String primary = "";
        String SQL ="SELECT primary_heatsource FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        primary = rs.getString(1);}
        return primary;
    }
    
    public static String getStructureSHeatsource(String name)throws SQLException
    {
        String secondary = "";
        String SQL ="SELECT IFNULL(secondary_heatsource, '') AS secondary_heatsource FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        secondary = rs.getString(1);}
        return secondary;
    }
    
    public static ArrayList<String> getStructureFurniture(int id) throws SQLException{
        ArrayList<String> furniture = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT h.name, f.quantity FROM furniture f, household_articles h WHERE f.idstructure =\"" + id + "\" AND h.idhousehold_articles  = f.idhousehold_articles " ;// WHERE users_user = \"" + user + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);   
        while (rs.next()) {
            furniture.add(rs.getString("h.name")+" "+rs.getString("f.quantity"));      
        }
        return furniture;
    }
    
    public static int getStructurePercent(String name)throws SQLException
    {
        int per = 0;
        String SQL ="SELECT percentage_incand FROM structure WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        per = ((Number) rs.getObject(1)).intValue();}
        return per;
    }
    
    //Hente områder
    public static ArrayList<String> getUserArea() throws SQLException{
        ArrayList<String> areas = new ArrayList<String>();
        String SQL = "SELECT name FROM area";// WHERE users_user = \"" + user + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        
        while (rs.next()) {
            areas.add(rs.getString("name"));
        }
        return areas;
    }
    
    public static int getAreaId(String name)throws SQLException
    {
        int id = 0;
        String SQL ="SELECT idarea FROM area WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        id = ((Number) rs.getObject(1)).intValue();}
        return id;
    }
    
    public static ArrayList<String> getAreaStructures(int id) throws SQLException{
        ArrayList<String> structures = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT s.name, a.quantity FROM structure s, area_structures a WHERE a.idarea =\"" + id + "\" AND a.idstructure  = s.idstructure " ;// WHERE users_user = \"" + user + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);   
        while (rs.next()) {
            structures.add(rs.getString("s.name")+" "+rs.getString("a.quantity"));      
        }
        return structures;
    }
    
    //Hente brukerens lagrede objekter
    public static ArrayList<String> getHouseholdArticle() throws SQLException{
        ArrayList<String> householdArticles = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT name FROM household_articles";// WHERE users_user = \"" + user + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        
        while (rs.next()) {
            householdArticles.add(rs.getString("name"));
        }
        
        
        return householdArticles;
    }
    //Henter objektets energiforbruk
    public static int getArticleEnergyUse(String name)throws SQLException
    {
        int energy = 0;
        String SQL ="SELECT energy_usage FROM household_articles WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        energy = ((Number) rs.getObject(1)).intValue();}
        return energy;
    }
    
    public static String getArticleCategory(String name)throws SQLException
    {
        String cat = "";
        String SQL ="SELECT category_category FROM household_articles WHERE name =\"" + name + "\"";
        ResultSet rs = sqlRead(getConnection(), SQL);
        while(rs.next()){
        cat = rs.getString(1);}
        return cat;
    }
    
    //For å få opp liste av beboere
    public static ArrayList<String> getInhabitants() throws SQLException{
        ArrayList<String> beboere = new ArrayList<String>();
        
        //SQL query command
        String SQL = "SELECT * FROM inhabitants";
        ResultSet rs = sqlRead(getConnection(), SQL);
        
        while (rs.next()) {
            beboere.add(rs.getString("name"));
        }
        
        return beboere;
    }
    
    //For å få opp liste av fylker
    public static ArrayList<String> getFylker() throws SQLException{
        ArrayList<String> fylker = new ArrayList<String>();
        ArrayList fylkeNr = new ArrayList();
        
        //SQL query command
        String SQL = "SELECT * FROM fylker";
        ResultSet rs = sqlRead(getConnection(), SQL);
        
        while (rs.next()) {
            fylker.add(rs.getString("fylkeNavn"));
            fylkeNr.add(rs.getString("fylkeID"));
        }
        
        return fylker;
    }

    //For å få opp liste av kommuner
    public static ArrayList<String> getKommuner(String fylkeNavn) throws SQLException{
        ArrayList<String> kommuner = new ArrayList<String>();
        ArrayList fylkeNr = new ArrayList();
        
        //SQL query command
        String SQL1 = "SELECT * FROM fylker WHERE fylkeNavn ='" + fylkeNavn + "'";
        ResultSet rs1 = sqlRead(getConnection(), SQL1);
        
        while (rs1.next()) {
            fylkeNr.add(rs1.getInt("fylkeID"));
        }
        
        //SQL query command
        String SQL = "SELECT * FROM kommuner WHERE fylkeID ='" + fylkeNr.get(0) + "'";
        ResultSet rs = sqlRead(getConnection(), SQL);
        
        while (rs.next()) {
            kommuner.add(rs.getString("kommuneNavn"));
        }
        
        return kommuner;    }
    
    
    //Hente brukerens lagrede simuleringer
    public static ArrayList<String> getSimulations(Connection con) throws SQLException{
        //Etter sprint 3
        
        ArrayList<String> simulations = new ArrayList<String>();
        
        return simulations;
    }
    
    //Sjekker om passord og brukernavn stemmmer
    public static boolean getUser( String user, String passwd){
        //SQL query command
        String SQL = "SELECT user FROM users WHERE user = \"" + user + "\" AND passwd = \"" + md5Hash(passwd) + "\"";
        try {
            ResultSet rs = sqlRead(getConnection(), SQL);
            if(!rs.next())
                return false;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseKobling.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        
        return true;
    }
    
    
    //Opprett bruker
    public static Boolean addUser(Connection con, String user, String passwd){
        
        try {
            //SQL query command
            String SQL = "INSERT INTO users(user, passwd) VALUES(?,?)";

            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, user);
            pstmt.setString(2, md5Hash(passwd));
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseKobling.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }

    //Legg inn objekter i databasen som bruker lager
    public static boolean addHouseholdArticle(String name, int energyUsage, String category, String user) {
        try {
            //SQL query command
            String SQL = "INSERT INTO household_articles(name, energy_usage, category_category, users_user) VALUES(?,?,?,?)";

            PreparedStatement pstmt = getConnection().prepareStatement(SQL);
            pstmt.setString(1, name);
            pstmt.setInt(2, energyUsage);
            pstmt.setString(3, category);
            pstmt.setString(4, user);
            pstmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseKobling.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
    
    //Legg inn hus i databasen som bruker lager
    public static boolean addStructure(Connection con, String user, String name, int livingArea, int age, String energyClass, String primaryHeatSource, String secondaryHeatSource, int incandescentBulbs){
        try {
            //SQL query command
            String SQL = "INSERT INTO structure(users_user, name, living_area, age, energy_class, primary_heatsource, secondary_heatsource, incandescent_bulbs) VALUES(?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt = con.prepareStatement(SQL);
  
            pstmt.setString(1, user);
            pstmt.setString(2, name);
            pstmt.setInt(3, livingArea);
            pstmt.setInt(4, age);
            pstmt.setString(5, energyClass);
            pstmt.setString(6, primaryHeatSource);
            pstmt.setString(7, secondaryHeatSource);
            pstmt.setInt(8, incandescentBulbs);
            pstmt.executeUpdate();
      
        } catch (Exception ex) {
            Logger.getLogger(DatabaseKobling.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
    
    //Legg inn område i databasen som bruker lager
    public static void addArea(Connection con){
        //Etter sprint 3
    }
        
    //Kjører sql kode
    public static ResultSet sqlRead(Connection con, String SQL) throws SQLException{
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(SQL);
       
        return rs;
    }
   
    //Sender tilbake en hash verdi av en string
    public static String md5Hash(String passwd){
        String password = null;
        byte[] md5ByteArray;
            
        try {
            md5ByteArray = passwd.getBytes("UTF-8");
            MessageDigest md = MessageDigest.getInstance("MD5");
            md.reset();
            md.update(md5ByteArray);
            md5ByteArray = md.digest(md5ByteArray);
            password = new String(md5ByteArray);
        } catch (Exception ex) {
            Logger.getLogger(DatabaseKobling.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return password;
    }
    
    
}
